package com.probiz.estore.order.dao.impl;

import java.math.BigDecimal;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;

import com.probiz.estore.Constants;
import com.probiz.estore.common.helper.ConfigUtil;
import com.probiz.estore.common.model.order.SalesOrder;
import com.probiz.estore.common.model.other.Tag;
import com.probiz.estore.common.model.system.Region;
import com.probiz.estore.common.util.DateUtil;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.search.SearchCriteria;
import com.probiz.estore.core.util.StringUtil;
import com.probiz.estore.order.OrderConstants;
import com.probiz.estore.order.dao.SalesOrderDao;
import com.probiz.estore.order.model.OrderSearchCriteria;
import com.probiz.estore.system.dao.RegionDao;
import com.probiz.estore.webapp.util.RequestContext;


/**
 * Dao implementation for SalesOrder.
 */
public class SalesOrderDaoImpl extends HibernateGenericDaoImpl<SalesOrder> implements SalesOrderDao {
	@Override
	protected void initFilterHqlMap() {
		filterHqlMap.put(SalesOrder.FilterHqlName.DEFAULT, "select s from SalesOrder s order by s.salesOrderId desc");
		filterHqlMap.put(SalesOrder.FilterHqlName.ORDER_TOTAL, "select count(*) from SalesOrder s");
	}

	private RegionDao regionDao;

	public void setRegionDao(RegionDao regionDao) {
		this.regionDao = regionDao;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#searchByCriteria(com.probiz.estore.order.model.OrderSearchCriteria,
	 *      com.probiz.estore.core.search.SearchCriteria)
	 */
	public List<SalesOrder> searchByCriteria(OrderSearchCriteria orderSearchCriteria, SearchCriteria searchCriteria) {
		// -begin:获得所有符合的标签id
		Set<Integer> tagIdSet = new HashSet<Integer>();
		if (StringUtils.isNotBlank(orderSearchCriteria.getTags())) {
			String[] tagNameArray = orderSearchCriteria.getTags().trim().replaceAll("\\s+", ",").split(",");
			for (int i = 0; i < tagNameArray.length; i++) {
				tagIdSet.addAll(this.findByHql("select t.tagId from Tag t where t.tagName like ? and t.module=?", "%" + tagNameArray[i] + "%", Tag.MODULE_ORDER));
			}
		}
		// -end:获得所有符合的标签id

		StringBuffer hqlsb = new StringBuffer(100);
		List<Object> paramList = new ArrayList<Object>();
		// start:构建HQL语句

		hqlsb.append("select o from SalesOrder o where 1=1");
		// -start:订单号
		if (StringUtils.isNotBlank(orderSearchCriteria.getOrderNo())) {
			hqlsb.append(" and o.orderNo like ?");
			paramList.add("%" + orderSearchCriteria.getOrderNo().trim() + "%");
		}
		// -end:订单号
		// -start:客户id
		if (null != orderSearchCriteria.getCustomerId()) {
			hqlsb.append(" and o.customer.appuserId = ?");
			paramList.add(orderSearchCriteria.getCustomerId());   
		}
		if(RequestContext.isFront()){
			//如果是前台的，则不允许客户id为空
			hqlsb.append(" and o.customer is not null");
		}
		// -end:客户id

		// -start:收货人姓名
		if (StringUtils.isNotEmpty(orderSearchCriteria.getOrderAddressFirstname())) {
			hqlsb.append(" and ( o.salesOrderId in (");
			hqlsb.append(" select oi.salesOrder.salesOrderId from OrderShipment oi where oi.shippingAddress.firstname like ?) ");
			hqlsb.append(" or ");
			hqlsb.append(" o.orderAddress.firstname like ?) ");
			for (int i = 0; i < 2; i++) {
				paramList.add("%" + orderSearchCriteria.getOrderAddressFirstname().trim() + "%");
			}
		}
		// -end:收货人姓名

		// -start:收货地区
		if (null != orderSearchCriteria.getRegionId()) {
			Region region = regionDao.getById(Integer.valueOf(orderSearchCriteria.getRegionId()));
			if (null != region) {
				hqlsb.append(" and ( o.salesOrderId in (");
				hqlsb.append(" select oi.salesOrder.salesOrderId from OrderShipment oi where oi.shippingAddress.regionFullName like ?)");
				hqlsb.append(" or ");
				hqlsb.append(" o.orderAddress.regionFullName like ?) ");
				for (int i = 0; i < 2; i++) {
						paramList.add("%" + region.getFullName() + "%");
				}
				
			}
		}
		// -end:收货地区

		// -start:客户账号或姓名
		if (StringUtils.isNotEmpty(orderSearchCriteria.getCustomerUsernameOrFirstname())) {
			hqlsb.append(" and (o.customerUsername like ? or o.customerFirstname like ?)");
			for (int i = 0; i < 2; i++) {
				paramList.add("%" + orderSearchCriteria.getCustomerUsernameOrFirstname().trim() + "%");
			}
		}
		// -end:客户账号或姓名
		
		// -start:商品名称
		if (StringUtils.isNotBlank(orderSearchCriteria.getProductName())) {
			hqlsb.append(" and o.salesOrderId in (");
			hqlsb.append(" select os.salesOrder.salesOrderId from OrderSku os where os.productName like ?)");
			paramList.add("%"+orderSearchCriteria.getProductName().trim()+"%");
		}
		// -end:商品名称

		// -start:货号
		if (StringUtils.isNotEmpty(orderSearchCriteria.getProductSkuCode())) {
			hqlsb.append(" and o.salesOrderId in (");
			hqlsb.append(" select os.salesOrder.salesOrderId from OrderSku os where os.productSkuCode like ?)");
			paramList.add("%"+orderSearchCriteria.getProductSkuCode().trim()+"%");
		}
		// -end:货号

		// -begin:标签名称
		if (StringUtils.isNotBlank(orderSearchCriteria.getTags())) {
			hqlsb.append(" and (");
			hqlsb.append(" 1=0 ");
			for (Integer tagId : tagIdSet) {
				hqlsb.append(" or  ','||o.tags||',' like ?");
				paramList.add("%," + tagId + ",%");
			}
			hqlsb.append(" )");

		}
		// -end:标签名称

		// -start:最低订单总额
		if (null != orderSearchCriteria.getLowestTotalAmount()) {
			hqlsb.append(" and o.totalAmount >= ?");
			paramList.add(orderSearchCriteria.getLowestTotalAmount());
		}
		// -end:最低订单总额

		// -start:最高订单总额
		if (null != orderSearchCriteria.getHighestTotalAmount()) {
			hqlsb.append(" and o.totalAmount <= ?");
			paramList.add(orderSearchCriteria.getHighestTotalAmount());
		}
		// -end:最高订单总额

		// -start:下订单开始时间
		if (StringUtils.isNotBlank(orderSearchCriteria.getStartCreateTime())) {
			try {
				Date date = DateUtil.convertStringToDate(ConfigUtil.getInstance().getDatePattern(), orderSearchCriteria.getStartCreateTime());
				Calendar calender = Calendar.getInstance();
				calender.setTime(date);
				calender.set(Calendar.HOUR_OF_DAY, 0);
				calender.set(Calendar.MINUTE, 0);
				calender.set(Calendar.SECOND, 0);
				hqlsb.append(" and o.createTime >= ?");
				paramList.add(calender.getTime());
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// -end:下订单开始时间

		// -start:下订单结束时间
		if (StringUtils.isNotBlank(orderSearchCriteria.getEndCreateTime())) {
			try {
				Date date = DateUtil.convertStringToDate(ConfigUtil.getInstance().getDatePattern(), orderSearchCriteria.getEndCreateTime());
				Calendar calender = Calendar.getInstance();
				calender.setTime(date);
				calender.add(Calendar.DAY_OF_YEAR, 1);
				calender.set(Calendar.HOUR_OF_DAY, 0);
				calender.set(Calendar.MINUTE, 0);
				calender.set(Calendar.SECOND, 0);
				hqlsb.append(" and o.createTime <= ?");
				paramList.add(calender.getTime());
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// -end:下订单结束时间

		// -begin:支付状态
		if (!ArrayUtils.isEmpty(orderSearchCriteria.getPaymentStatuses())) {
			String multiObjString = StringUtil.arrayToString(orderSearchCriteria.getPaymentStatuses(), ",");
			if (StringUtils.isNotEmpty(multiObjString)) {
				hqlsb.append(" and (");
				hqlsb.append("o.paymentStatus in (").append(multiObjString).append(")");
				hqlsb.append(" )");
			}
		}
		// -end:支付状态

		// -begin:发货状态
		if (!ArrayUtils.isEmpty(orderSearchCriteria.getShipmentStatuses())) {
			String multiObjString = StringUtil.arrayToString(orderSearchCriteria.getShipmentStatuses(), ",");
			if (StringUtils.isNotEmpty(multiObjString)) {
				hqlsb.append(" and (");
				hqlsb.append("o.shipmentStatus in (").append(multiObjString).append(")");
				hqlsb.append(" )");
			}
		}
		// -end:发货状态

		// -begin:基础状态
		if (!ArrayUtils.isEmpty(orderSearchCriteria.getOrderStatuses())) {
			String multiObjString = StringUtil.arrayToString(orderSearchCriteria.getOrderStatuses(), ",");
			if (StringUtils.isNotEmpty(multiObjString)) {
				hqlsb.append(" and (");
				hqlsb.append("o.orderStatus in (").append(multiObjString).append(")");
				hqlsb.append(" )");
			}
		}
		// -end:基础状态

		// -begin:支付方式
		if (!ArrayUtils.isEmpty(orderSearchCriteria.getPaymentMethods())) {
			String multiObjString = StringUtil.arrayToString(orderSearchCriteria.getPaymentMethods(), ",");
			if (StringUtils.isNotEmpty(multiObjString)) {
				hqlsb.append(" and ( o.salesOrderId in ( select op.salesOrder.salesOrderId from OrderPayment op where ");
				hqlsb.append("op.paymentMethodId in (").append(multiObjString).append(") )");
				hqlsb.append(" or ");
				hqlsb.append(" o.paymentMethodId in (").append(multiObjString).append(") )");
			}
		}
		// -end:支付方式

		// -begin:配送方式
		if (!ArrayUtils.isEmpty(orderSearchCriteria.getShippingMethods())) {
			String multiObjString = StringUtil.arrayToString(orderSearchCriteria.getShippingMethods(), ",");
			if (StringUtils.isNotEmpty(multiObjString)) {
				hqlsb.append(" and ( o.salesOrderId in ( select oi.salesOrder.salesOrderId from OrderShipment oi where ");
				hqlsb.append("oi.shippingMethodId in (").append(multiObjString).append(") )");
				hqlsb.append(" or ");
				hqlsb.append(" o.shippingMethodId in (").append(multiObjString).append(") )");
			}
		}
		// -end:配送方式
		
		// -begin:快速过滤
		if(null != orderSearchCriteria.getQuickFilter()){
			Calendar calendar = Calendar.getInstance();
			switch(orderSearchCriteria.getQuickFilter()){
			case 0://全部订单
				break;
			case 1://等待确认
				hqlsb.append(" and o.orderStatus = ?");
				paramList.add(OrderConstants.ORDER_STATUS_AWAITING_CONFIRMATION);
				break;
			case 2://等待先付款
				hqlsb.append(" and o.isCod = ?");
				hqlsb.append(" and (o.paymentStatus = ? or o.paymentStatus = ?)");
				paramList.add(Constants.FLAG_FALSE);
				paramList.add(OrderConstants.PAYMENT_STATUS_UNPAID);
				paramList.add(OrderConstants.PAYMENT_STATUS_PARTIALLY_PAID);
				break;
			case 3://已付款待发货
				hqlsb.append(" and o.isCod = ?");
				hqlsb.append(" and (o.paymentStatus = ? or o.paymentStatus = ?)");
				hqlsb.append(" and (o.shipmentStatus = ? or o.shipmentStatus = ?)");
				paramList.add(Constants.FLAG_FALSE);
				paramList.add(OrderConstants.PAYMENT_STATUS_ALL_PAID);
				paramList.add(OrderConstants.PAYMENT_STATUS_PARTIALLY_PAID);
				paramList.add(OrderConstants.SHIPMENT_STATUS_UNSHIP);
				paramList.add(OrderConstants.SHIPMENT_STATUS_PARTIALLY_SHIPPED);
				break;
			case 4://等待先发货
				hqlsb.append(" and o.isCod = ?");
				hqlsb.append(" and (o.shipmentStatus = ? or o.shipmentStatus = ?)");
				paramList.add(Constants.FLAG_TRUE);
				paramList.add(OrderConstants.SHIPMENT_STATUS_UNSHIP);
				paramList.add(OrderConstants.SHIPMENT_STATUS_PARTIALLY_SHIPPED);
				break;
			case 5://已发货待付款
				hqlsb.append(" and o.isCod = ?");
				hqlsb.append(" and (o.paymentStatus = ? or o.paymentStatus = ?)");
				hqlsb.append(" and (o.shipmentStatus = ? or o.shipmentStatus = ?)");
				paramList.add(Constants.FLAG_TRUE);
				paramList.add(OrderConstants.PAYMENT_STATUS_UNPAID);
				paramList.add(OrderConstants.PAYMENT_STATUS_PARTIALLY_PAID);
				paramList.add(OrderConstants.SHIPMENT_STATUS_ALL_SHIPPED);
				paramList.add(OrderConstants.SHIPMENT_STATUS_PARTIALLY_SHIPPED);
				break;
			case 6://已发货
				hqlsb.append(" and (o.shipmentStatus = ? or o.shipmentStatus = ?)");
				paramList.add(OrderConstants.SHIPMENT_STATUS_ALL_SHIPPED);
				paramList.add(OrderConstants.SHIPMENT_STATUS_PARTIALLY_SHIPPED);
				break;
			case 7://已退货
				hqlsb.append(" and (o.shipmentStatus = ? or o.shipmentStatus = ?)");
				paramList.add(OrderConstants.SHIPMENT_STATUS_ALL_RETURNED);
				paramList.add(OrderConstants.SHIPMENT_STATUS_PARTIALLY_RETURNED);
				break;
			case 8://已退款
				hqlsb.append(" and (o.paymentStatus = ? or o.paymentStatus = ?)");
				paramList.add(OrderConstants.PAYMENT_STATUS_ALL_REFUND);
				paramList.add(OrderConstants.PAYMENT_STATUS_PARTIALLY_REFUND);
				break;
			case 9: //待付款(客户视图)	
				hqlsb.append(" and o.orderStatus in (?,?,?)");
				hqlsb.append(" and o.paymentStatus in (?,?)");
				paramList.add(OrderConstants.ORDER_STATUS_AWAITING_CONFIRMATION);
				paramList.add(OrderConstants.ORDER_STATUS_CONFIRMED);
				paramList.add(OrderConstants.ORDER_STATUS_HANDLING);
				paramList.add(OrderConstants.PAYMENT_STATUS_UNPAID);
				paramList.add(OrderConstants.PAYMENT_STATUS_PARTIALLY_PAID);
				break;
			case 10: //最近一周(客户视图)	
				calendar.add(Calendar.DAY_OF_YEAR, -7);
				hqlsb.append(" and o.createTime >= ?");
				paramList.add(calendar.getTime());
				break;	
			default:break;	
			}
		}
		
		
		// -end:  快速过滤
		
		//start:order by
        if (null != orderSearchCriteria.getOrderby()) {
            hqlsb.append(" order by ").append(getSortByString(orderSearchCriteria.getOrderby()));
        }else{
        	hqlsb.append(" order by o.salesOrderId desc");
        }
        //end:order by
		
		// end: 构建HQL语句
		searchCriteria.setHql(hqlsb.toString());
		searchCriteria.removeParamValues();
		for (Object object : paramList) {
			searchCriteria.addParamValue(object);
		}
		List<SalesOrder> orderList = searchByCriteria(searchCriteria);
		return orderList;
	}
	
	
	 /**
     * 根据orderby获得 hql语句
     *
     * @param orderby 排序id
     * @return hql语句
     */
    private String getSortByString(short orderby) {
        String orderByString;
        switch (orderby) {
        	case 0: //前台默认
                 orderByString = "o.salesOrderId desc";
                 break;
            case 1:
                orderByString = "o.orderNo asc";
                break;
            case 2:
                orderByString = "o.orderNo desc";
                break;
            case 3:
            	orderByString = "o.createTime asc";
            	break;
            case 4:
            	orderByString = "o.createTime desc";
            	break;
            case 5:
            	orderByString = "o.totalAmount asc";
            	break;
            case 6:
            	orderByString = "o.totalAmount desc";
            	break;
            case 7:
            	orderByString = "convert_gbk(o.orderAddress.firstname) asc";
            	break;
            case 8:
            	orderByString = "convert_gbk(o.orderAddress.firstname) desc";
            	break;
            case 9:
            	orderByString = "convert_gbk(o.paymentTypeDesc) asc";
            	break;
            case 10:
            	orderByString = "convert_gbk(o.paymentTypeDesc) desc";
            	break;
            case 11:
            	orderByString = "convert_gbk(o.deliveryTypeDesc) asc";
            	break;
            case 12:
            	orderByString = "convert_gbk(o.deliveryTypeDesc) desc";
            	break;
            case 13:
            	orderByString = "o.paymentStatus asc";
            	break;
            case 14:
            	orderByString = "o.paymentStatus desc";
            	break;
            case 15:
            	orderByString = "o.shipmentStatus asc";
            	break;
            case 16:
            	orderByString = "o.shipmentStatus desc";
            	break;
            case 17:
            	orderByString = "o.orderStatus asc";
            	break;
            case 18:
            	orderByString = "o.orderStatus desc";
            	break;
            default:
                orderByString = "o.salesOrderId desc";
                break;
        }
        return orderByString;
    }

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#countCustomerOrder(java.lang.Integer,
	 *      boolean)
	 */
	public long countCustomerOrder(Integer userId, boolean complete) {
		String hql = "from SalesOrder so where so.customer.appuserId=?";
		List params = new ArrayList();
		params.add(userId);
		if (complete) {
			hql += " and so.orderStatus=?";
			params.add(OrderConstants.ORDER_STATUS_COMPLETE);
		}
		return this.countByHql(hql, params.toArray());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#countNewArrivalOrder()
	 */
	public Long countNewArrivalOrder() {
		 String hql="select count(*) from SalesOrder o where o.orderStatus=? )";
		 return this.countByHql(hql, OrderConstants.ORDER_STATUS_AWAITING_CONFIRMATION);
	}

	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.order.dao.SalesOrderDao#countUnPaidSalesOrder(java.lang.Integer)
	 */
	public Long countUnPaidSalesOrder(Integer customerId){
		String hql="select count(*) from SalesOrder o where  o.orderStatus in (?,?,?) and o.paymentStatus in (?,?) and o.customer.appuserId = ? )";
		return this.countByHql(hql, OrderConstants.ORDER_STATUS_AWAITING_CONFIRMATION, OrderConstants.ORDER_STATUS_CONFIRMED, OrderConstants.ORDER_STATUS_HANDLING,OrderConstants.PAYMENT_STATUS_UNPAID,OrderConstants.PAYMENT_STATUS_PARTIALLY_PAID,customerId);
	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#getLastSalesOrder(java.lang.Integer)
	 */
	public SalesOrder getLastSalesOrder(Integer customerId) {
		String hql = "select so from SalesOrder so where  so.customer.appuserId=? order by so.createTime desc";
		List result = this.findByHql(hql, customerId);
		if (result != null && result.size() > 0) {
			return (SalesOrder) result.get(0);
		} else {
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#countCustomerOrder(java.lang.Integer,
	 *      java.lang.Short)
	 */
	public long countCustomerOrder(Integer userId, Short orderStatus) {
		Long result = this.countByHql("select count(s) from SalesOrder s where s.customer.appuserId=? and s.orderStatus=?", userId, orderStatus);
		return result.longValue();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.order.dao.SalesOrderDao#getCustomerConsumption(java.lang.Integer,
	 *      java.util.Date, java.util.Date)
	 */
	public BigDecimal getCustomerConsumption(Integer userId, Date startDate, Date endDate) {
		String hql = "select sum(s.totalAmount) from SalesOrder s where s.customer.appuserId=?";
		if (startDate != null && endDate != null) {
			hql = hql + " and s.createTime between ? and ?";
		}
		hql = hql + " and s.orderStatus>=0";
		List result = null;
		if (startDate != null && endDate != null) {
			result = this.findByHql(hql, userId, startDate, endDate);
		} else {
			result = this.findByHql(hql, userId);
		}
		if (result != null && result.size() > 0) {
			return (BigDecimal) result.get(0);
		} else {
			return BigDecimal.ZERO;
		}
	}


	public List<SalesOrder> getSalesOrder4Export(Date latestExportTime) {
		return null;
	}

}
